import pandas as pd


def tosql(filepath,tablename,savesql,model):
    df = pd.read_excel(filepath,dtype=str)

    # print(df.columns.to_list)

    indexlist = []
    for i in range(df.shape[1]):
        # print(df.columns[i])
        indexlist.append(df.columns[i])
    indexstr = '('+str(indexlist)[1:-1]+')'
    indexstr = indexstr.replace("'","")
    # print(indexstr)

    sqlstr = ''
    for row in df.itertuples():
        list = []
        for li in range(df.shape[1]):
            # print(row[li+1])
            # print(type(row[li+1]))

            if str(row[li+1]).strip() == 'nan':
                # print(8888)
                if model == 0:
                    list.append('')
                # row[li+1]='NULL'  #'Pandas' object does not support item assignment
                if model == 1:
                    list.append('NULL')
                continue

            if str(row[li+1]).strip() == '\\N':
                # print(8888)
                if model == 0:
                    list.append('')
                # row[li+1]='NULL'  #'Pandas' object does not support item assignment
                if model == 1:
                    list.append('NULL')
                continue

            list.append(row[li+1])
        # print(str(list)[1:-1])
        sqlline = '('+str(list)[1:-1]+'),\n'
        sqlstr=sqlstr+sqlline

    sql = sqlstr[0:-2]+';'
    # print(sql)

    #整合成sql语句
    aggSql = "insert into "+tablename+" "+indexstr+" values \n" +sql

    if model == 1:
        print("model=={},开启空格转换成NULL".format(model))
        aggSql = aggSql.replace("'NULL'",'NULL')
        aggSql = aggSql.replace("'\\N'",'NULL')



    # print(aggSql)

    file_write_obj=open(savesql, 'w', encoding="utf-8")  # 新文件
    file_write_obj.write(aggSql)
    file_write_obj.close()
    print("保存文件成功")



if __name__ == '__main__':
    #excel文件
    # excelfile = '数据测试集\\excel2sql.xls'
    excelfile = 'C:\\Users\\11578\\Desktop\\河北.xlsx'
    #表名
    tablename = "itsm_complain_analyze_data"
    #生成一个.sql文件的存放路径
    # savesql = '数据结果集\\excel2sql.sql'
    savesql = 'C:\\Users\\11578\\Desktop\\excel2sql.sql'

    # model == 0 的时候，空的地方用空字符串代替，model == 1的时候，空的地方或者为\N 用NULL替换 (有的数据库软件导出是\N，有的为空)
    model=1
    tosql(excelfile, tablename, savesql, model)